

from dbtools import *
from Student import *

class StudentDAO:

    # 定义一个增加数据的方法
    def insert(self,stu):
        conn = Dbtools.get_connection()           # 连接数据库
        cursor = Dbtools.get_cursor(conn)         # 建立游标
        sql = f"insert into student value({stu.student_no},'{stu.student_name}','{stu.student_contact}',{stu.class_no})"
        cursor.execute(sql)                       # 执行增加数据的操作
        Dbtools.commit_and_close(conn,cursor)     # 释放资源

    # 定义一个删除数据的方法
    def delete_by_no(self,no):
        conn = Dbtools.get_connection()
        cursor = Dbtools.get_cursor(conn)
        sql = f"delete from student where student_no = {no}"
        cursor.execute(sql)
        Dbtools.commit_and_close(conn,cursor)

    # 定义一个更改数据的方法
    def update(self,stu):
        conn = Dbtools.get_connection()
        cursor = Dbtools.get_cursor(conn)
        sql = f"update student set student_name = '{stu.student_name}',student_contact = '{stu.student_contact}',class_no = {stu.class_no} where student_no = {stu.student_no}"
        cursor.execute(sql)
        Dbtools.commit_and_close(conn,cursor)

    # 定义一个查询数据的方法  (查询一条数据)
    def find_by_no(self,no):
        conn = Dbtools.get_connection()
        cursor = Dbtools.get_cursor(conn)
        sql = f"select * from student where student_no = {no}"
        cursor.execute(sql)
        data = cursor.fetchone()
        stu = Student(data['student_no'],data['student_name'],data['student_contact'],data['class_no'])
        Dbtools.commit_and_close(conn,cursor)
        return stu

    # 定义一个查询所有数据的方法（查询所有人）
    def find_all(self):
        conn = Dbtools.get_connection()
        cursor = Dbtools.get_cursor(conn)
        sql = f"select * from student"
        cursor.execute(sql)
        data = cursor.fetchall()
        list = []
        list_temp = []
        for i in range(len(data)):

            stu = Student(data[i]['student_no'],data[i]['student_name'],data[i]['student_contact'],data[i]['class_no'])

            list.append(stu)

        Dbtools.commit_and_close(conn,cursor)
        return list